﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Data;
using System.Data.SqlClient;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class Entity
    {
        public Entity()
        {

        }

        public static Guid GetIDFromName(string entityName, DTO.EntityClass entityClass, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Guid entityID;
            const string sprocname = "[ServiceEngine].[GetIDFromName]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter entityNameParam = new SqlParameter("@EntityName", SqlDbType.NVarChar, 400);
                    SqlParameter entityTypeIDParam = new SqlParameter("@EntityTypeID", SqlDbType.Int);
                    SqlParameter entityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    entityIDParam.Direction = ParameterDirection.Output;

                    cmd.CommandType = CommandType.StoredProcedure;
                    entityNameParam.Value = entityName;
                    entityTypeIDParam.Value = (int)entityClass;
                    cmd.Parameters.Add(entityNameParam);
                    cmd.Parameters.Add(entityTypeIDParam);
                    cmd.Parameters.Add(entityIDParam);

                    cmd.ExecuteNonQuery();

                    if (entityIDParam.Value != DBNull.Value)
                    {
                        entityID = (Guid)entityIDParam.Value;
                    }
                    else
                    {
                        entityID = Guid.Empty;
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return entityID;

        }

        public static DTO.Entity GetNameFromID(Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[GetNameFromID]";
            DTO.Entity entity = new DTO.Entity();

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter entityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    entityIDParam.Value = entityID;
                    cmd.Parameters.Add(entityIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            entity.ID = entityID;
                            entity.Name = reader.GetString(0);
                            entity.EntityClass = (DTO.EntityClass)reader.GetInt16(1);
                            if (reader.IsDBNull(2))
                            {
                                entity.Description = null;
                            }
                            else
                            {
                                entity.Description = reader.GetString(2);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return entity;

        }

        public static bool EntityExists(Guid entityID, DTO.EntityClass entityClass, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool exists;
            const string sprocname = "[ServiceEngine].[EntityExists]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter entityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    SqlParameter entityTypeIDParam = new SqlParameter("@EntityTypeID", SqlDbType.Int);
                    SqlParameter entityExistsParam = new SqlParameter("@EntityExists", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    entityIDParam.Value = entityID;
                    entityExistsParam.Direction = ParameterDirection.Output;
                    entityTypeIDParam.Value = (int)entityClass;
                    cmd.Parameters.Add(entityExistsParam);
                    cmd.Parameters.Add(entityTypeIDParam);
                    cmd.Parameters.Add(entityIDParam);

                    cmd.ExecuteNonQuery();

                    exists = (bool)entityExistsParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return exists;
        }

        public static DTO.EntityGroup GetEntityGroup(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EntityGroup entityGroup = new DTO.EntityGroup();
            const string sprocname = "[ServiceEngine].[GetEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            entityGroup.ID = reader.GetGuid(0);
                            entityGroup.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                entityGroup.Description = null;
                            }
                            else
                            {
                                entityGroup.Description = reader.GetString(2);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return entityGroup;

        }

        public static Guid CreateEntityGroup(string groupName, string groupDesc, Int16 entityTypeID, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter EntityTypeParam = new SqlParameter("@EntityTypeID", SqlDbType.SmallInt);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = groupName;
                    DescriptionParam.Value = groupDesc;
                    EntityTypeParam.Value = entityTypeID;


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(EntityTypeParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;

                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static void AssignEntityToEntityGroup(Guid entityGroupID, Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignEntityToEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EntityGroupIDParam = new SqlParameter("@EntityGroupID", SqlDbType.UniqueIdentifier);
                    SqlParameter EntityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EntityGroupIDParam.Value = entityGroupID;
                    cmd.Parameters.Add(EntityGroupIDParam);
                    EntityIDParam.Value = entityID;
                    cmd.Parameters.Add(EntityIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemoveEntityFromEntityGroup(Guid entityGroupID, Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEntityFromEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EntityGroupIDParam = new SqlParameter("@EntityGroupID", SqlDbType.UniqueIdentifier);
                    SqlParameter EntityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EntityGroupIDParam.Value = entityGroupID;
                    cmd.Parameters.Add(EntityGroupIDParam);
                    EntityIDParam.Value = entityID;
                    cmd.Parameters.Add(EntityIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static Guid UpdateEntityGroup(Guid groupID, string groupName, string groupDesc, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Value = groupID;
                    NameParam.Value = groupName;
                    DescriptionParam.Value = groupDesc;


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }
                //get existing versions

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return groupID;
        }

        public static DTO.EntityGroupList ListEntityGroupsByEntity(Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EntityGroupList list = new DTO.EntityGroupList();
            const string sprocname = "[ServiceEngine].[ListEntityGroupsByEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityID", SqlDbType.UniqueIdentifier);
                    IDParam.Value = entityID;
                    cmd.Parameters.Add(IDParam);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.EntityGroups = new List<DTO.EntityGroupListElement>();
                            while (reader.Read())
                            {
                                DTO.EntityGroupListElement element = new DTO.EntityGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.EntityGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }

        public static DTO.EntityGroupList ListEntityGroups(Int16 entityTypeID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EntityGroupList list = new DTO.EntityGroupList();
            const string sprocname = "[ServiceEngine].[ListEntityGroups]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityTypeID", SqlDbType.SmallInt);
                    IDParam.Value = entityTypeID;
                    cmd.Parameters.Add(IDParam);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.EntityGroups = new List<DTO.EntityGroupListElement>();
                            while (reader.Read())
                            {
                                DTO.EntityGroupListElement element = new DTO.EntityGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.EntityGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }

        public static void DeleteEntityGroup(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteEntityGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;

                    NameParam.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);

                    cmd.ExecuteNonQuery();

                    string operationGroupName = (string)NameParam.Value;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

    }
}
